package com.cpit.qualitycontrol.dao.jpqlquery;

import com.cpit.qualitycontrol.entity.Device;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.TypedQuery;
import java.util.Iterator;
import java.util.List;

@Component
public class DeviceQueryRepository {
    @Autowired
    private EntityManagerFactory entityManagerFactory = null;

    /**
     * 根据可变条件查询流程规则对应的分页
     *
     * @param productLineId 产品线Id
     * @param projectName   项目名称
     * @param deviceTypeId  设备类型ID
     * @param deviceNum     设备编号
     * @param pageable      分页排序条件
     * @return 符合查询条件的流程规则分页对象
     */
    public Page<Device> findByCondition(Long productLineId, String projectName, Long deviceTypeId, String deviceNum,
                                        String addrs, Pageable pageable) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        //查询记录总数
        String strJpqlCountSql = getJpqlCountSqlFixCondition().append(getSqlPartOfCondition(productLineId, projectName,
                deviceTypeId, deviceNum, addrs)).toString();
        Long recordCount = (Long) entityManager.createQuery(strJpqlCountSql).getSingleResult();

        //分页查询
        String strJpqlSql = getJpqlSqlFixCondition().append(getSqlPartOfCondition(productLineId, projectName,
                deviceTypeId, deviceNum, addrs)).append(getSqlPartOfOrderBy(pageable)).toString();
        TypedQuery<Device> typedQuery = entityManager.createQuery(strJpqlSql, Device.class);
        typedQuery.setMaxResults(pageable.getPageSize());   //计数从0开始
        typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());

        return new PageImpl<>(typedQuery.getResultList(), pageable, recordCount);
    }

    /**
     * 根据可变条件查询设备列表
     *
     * @param productLineId 产品线Id
     * @param projectName   项目名称
     * @param deviceTypeId  设备类型ID
     * @param deviceNum     设备编号
     * @return 符合查询条件的項目
     */
    public List<Device> findByCondition(Long productLineId, String projectName, Long deviceTypeId, String deviceNum, String addrs) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        String strJpql = getJpqlSqlFixCondition().append(getSqlPartOfCondition(productLineId, projectName, deviceTypeId, deviceNum, addrs)).toString();
        TypedQuery<Device> typedQuery = entityManager.createQuery(strJpql, Device.class);

        return typedQuery.getResultList();
    }

    /**
     * 根据主键ID查询设备
     *
     * @param id 主键ID
     * @return 设备
     */
    public Device findById(String id) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();
        String strJpql = getJpqlSqlFixCondition().append(" and dvc.id = '").append(id).append("'").toString();
        TypedQuery<Device> typedQuery = entityManager.createQuery(strJpql, Device.class);

        return typedQuery.getSingleResult();
    }

    /**
     * 生成含固定条件查询记录的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getJpqlSqlFixCondition() {
        return new StringBuilder("SELECT new com.cpit.qualitycontrol.entity.Device(dvc.id, dvc.name, dvc.addrs,dt.id, dt.name, dvc.version, " +
                "dvc.vendor, dvc.longitude, dvc.latitude, pj.id, pj.name, pl.id,  pl.name, dvc.source, dvc.note)")
                .append(" from Device dvc, DeviceType dt, ProductLine pl, Project pj ")
                .append(" where dvc.deviceTypeId=dt.id and dvc.projectId=pj.id and dvc.productLineId=pl.id");
    }

    /**
     * 生成含固定条件查询记录数量的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getJpqlCountSqlFixCondition() {
        return new StringBuilder("SELECT count(dvc)")
                .append(" from Device dvc, DeviceType dt, ProductLine pl, Project pj ")
                .append(" where dvc.deviceTypeId=dt.id and dvc.projectId=pj.id and dvc.productLineId=pl.id");
    }

    /**
     * 生成动态可变条件部分字符串
     *
     * @param productLineId 产品线Id
     * @param projectName   项目名称
     * @param deviceTypeId  设备类型ID
     * @param deviceNum     设备编号
     * @return 可变条件字符串对象
     */
    private StringBuilder getSqlPartOfCondition(Long productLineId, String projectName, Long deviceTypeId, String deviceNum, String addrs) {
        StringBuilder sbCondition = new StringBuilder("");
        if (addrs != null && !addrs.isEmpty()) {
            sbCondition.append(" and dvc.addr like '%").append(addrs).append("%'");
        }
        if (deviceNum != null && !deviceNum.isEmpty()) {
            sbCondition.append(" and dvc.id like '%").append(deviceNum).append("%'");
        }
        if (projectName != null && !projectName.equals("")) {
            sbCondition.append(" and pj.name   like '%").append(projectName).append("%'");
        }
        if (deviceTypeId != null && deviceTypeId != 0) {
            sbCondition.append(" and dvc.deviceTypeId = ").append(deviceTypeId);
        }
        if (productLineId != null && productLineId != 0) {
            sbCondition.append(" and dvc.productLineId = ").append(productLineId);
        }

//        sbCondition.append(" ORDER BY dvc.createTime DESC");

        return sbCondition;
    }

    /**
     * 生成JPQL语句中排序部分
     *
     * @param pageable 含排序的分页对象Pageable对象
     * @return 排序部分字符串对象
     */
    private StringBuilder getSqlPartOfOrderBy(Pageable pageable) {
        final String STRING_ORDER_BY = " order by ";
        StringBuilder stringBuilder = new StringBuilder("");

        Sort sort = pageable.getSort();
        if (sort != null) {
            stringBuilder.append(STRING_ORDER_BY);
            Iterator<Sort.Order> iterator = sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                if (order == null)
                    break;
                String orderProperty = order.getProperty();
                if (orderProperty.equals("deviceTypeName"))
                    stringBuilder.append("dt.name");
                else if (orderProperty.equals("productLineName"))
                    stringBuilder.append("pl.name");
                else if (orderProperty.equals("projectName"))
                    stringBuilder.append("pj.name");
                else if (orderProperty.equals("id"))
                    stringBuilder.append("dvc.id");
                else
                    stringBuilder.append("dvc.").append(orderProperty);
                stringBuilder.append(" ").append(order.getDirection().name()).append(",");
            }
        }
        if (stringBuilder.lastIndexOf(",") > 0)
            stringBuilder.setLength(stringBuilder.length() - 1);
        if (stringBuilder.length() == STRING_ORDER_BY.length())
            stringBuilder.setLength(0);

        return stringBuilder;
    }

}
